-- STANDARD SCRIPT TO LOAD THE EMDEON TABLE 
--
--


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION emdeon_out_f() RETURNS integer AS $$
DECLARE
  v_status integer := 0;
  v_batch_run_date timestamp;
  v_status_unique integer;
  v_status_tpl integer;
  v_status_commercial integer;
  v_status_medicaid integer;
  v_status_medicare_slow integer;
  v_status_medicare_fast integer;
  v_status_chg float;
  v_status_bal float;
  v_status_tpl_chg float;
  v_status_tpl_bal float;
  v_status_commercial_chg float;
  v_status_commercial_bal float;
  v_status_medicaid_chg float;
  v_status_medicaid_bal float;
  v_status_medicare_slow_chg float;
  v_status_medicare_slow_bal float;
  v_status_medicare_fast_chg float;
  v_status_medicare_fast_bal float ;
  v_cust_id integer;

BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,6,'emdeon_out_t INSERT BEGIN');


-- STEP 1: TRUNCATE TABLE
TRUNCATE emdeon_out_t;


-- STEP 2: INSERT DATA FROM emdeon_out_t TO BE SENT TO EMDEON: WE OUTPUT FIRST TO HIT NON-COMMERCIAL DATABASE
-- IN THIS CASE WE ALWAYS USE THE PATIENT NAME 

INSERT INTO emdeon_out_t
  SELECT
    c.emdeon_tpl_flag,
    c.emdeon_commercial_flag,
    c.emdeon_medicare_flag,
    c.emdeon_medicaid_flag,  
    c.client_npi,
    c.npi,
    c.tax_id,
    c.entity_name,
    c.entity_pay_to_street,
    c.entity_pay_to_city,
    c.entity_pay_to_state,
    c.entity_pay_to_zip,
    c.entity_information,
    c.entity_phone,
    c.emdeon_matchkey,
    c.service_date_begin,
    c.service_date_end,
    c.fname,
    c.mname,
    c.lname,
    c.dob,
    c.gender,
    c.ssn,
    c.addr1,
    c.addr2,
    c.city,
    c.state,
    c.zip5,
    'P' as scr_code,
    c.legacy_pin,
    c.cust_id

  FROM

  (SELECT
          a.emdeon_tpl_flag,
          a.emdeon_commercial_flag,
          a.emdeon_medicare_flag,
          a.emdeon_medicaid_flag,
          a.cust_id,
          a.client_npi,
          a.emdeon_matchkey,
          a.service_date_begin,
          a.service_date_end,
          a.fname,
          a.mname,
          a.lname,
          a.dob,
          a.gender,
          a.ssn,
          a.addr1,
          a.addr2,
          a.city,
          a.state,
          a.zip5,
          b.npi,
          b.tax_id,
          b.entity_name,
          b.entity_pay_to_street,
          b.entity_pay_to_city,
          b.entity_pay_to_state,
          b.entity_pay_to_zip,
          b.entity_information,
          b.entity_phone,
          b.legacy_pin

   FROM

          (SELECT 

               'N' as emdeon_tpl_flag,
               'N' as emdeon_commercial_flag,
               (case WHEN emdeon_medicare_flag in ('1','2') and emdeon_medicare_fast = '1' THEN 'Y1'
                     WHEN emdeon_medicare_flag in ('1','2')                                THEN 'Y2' 
                     ELSE                                                                       'N'
                     END) as emdeon_medicare_flag,
               (case when emdeon_medicaid_flag in ('1','2') THEN state 
                     ELSE                                        NULL 
                     END) as emdeon_medicaid_flag,
               cust_id,
               client_npi2 as client_npi,
               'P-'||hid||'-'||acctnum as emdeon_matchkey,
               service_date_begin,
               (CASE WHEN service_date_end IS NULL THEN service_date_begin
                     ELSE                               service_date_end
                     END) as service_date_end,
               fname,
               mname,
               lname,
               dob,
               gender,
               ssn,
               addr1,
               addr2,
               city,
               state,
               zip5 

            FROM 
               batch_insurance1_t 
            WHERE 
               emdeon_medicaid_flag in ('1','2') OR emdeon_medicare_flag in ('1','2') 

          ) as a 

     	 JOIN

    	(SELECT 
                cust_id,
                client_npi,
                npi,
                tax_id,
                entity_name,
                entity_pay_to_street,
                entity_pay_to_city,
                entity_pay_to_state,
                entity_pay_to_zip,
                entity_information,
                entity_phone,
       		(case when pin    is NULL THEN legacy
                      when legacy is NULL THEN ','||pin
             	      else                     legacy||','||pin
                      end) as legacy_pin
     	FROM
       		insurance0_npi_t
        WHERE
                npi_gov_ind = 1 
    	) as b

    	ON (a.cust_id = b.cust_id AND a.client_npi = b.client_npi)     

  ) as c
;




-- STEP 3: INSERT DATA FROM emdeon_out_t TO BE SENT TO EMDEON: WE OUTPUT SECOND TO HIT COMMERCIAL DATABASE
-- IN THIS CASE WE USE THE PATIENT NAME OR THE GUARANTOR NAME DEPENDING ON THE VALUE OF THE name_type FIELD: P or G


INSERT INTO emdeon_out_t
  SELECT
    c.emdeon_tpl_flag,
    c.emdeon_commercial_flag,
    c.emdeon_medicare_flag,
    c.emdeon_medicaid_flag,
    c.client_npi,
    c.npi,
    c.tax_id,
    c.entity_name,
    c.entity_pay_to_street,
    c.entity_pay_to_city,
    c.entity_pay_to_state,
    c.entity_pay_to_zip,
    c.entity_information,
    c.entity_phone,
    c.emdeon_matchkey,
    c.service_date_begin,
    c.service_date_end,
    c.fname,
    c.mname,
    c.lname,
    c.dob,
    c.gender,
    c.ssn,
    c.addr1,
    c.addr2,
    c.city,
    c.state,
    c.zip5,
    'P' as scr_code,
    c.legacy_pin,
    c.cust_id

  FROM

  (SELECT
          a.emdeon_tpl_flag,
          a.emdeon_commercial_flag,
          a.emdeon_medicare_flag,
          a.emdeon_medicaid_flag,
          a.cust_id,
          a.client_npi,
          a.emdeon_matchkey,
          a.service_date_begin,
          a.service_date_end,
          a.fname,
          a.mname,
          a.lname,
          a.dob,
          a.gender,
          a.ssn,
          a.addr1,
          a.addr2,
          a.city,
          a.state,
          a.zip5,
          b.npi,
          b.tax_id,
          b.entity_name,
          b.entity_pay_to_street,
          b.entity_pay_to_city,
          b.entity_pay_to_state,
          b.entity_pay_to_zip,
          b.entity_information,
          b.entity_phone,
          b.legacy_pin


   FROM

         (SELECT
               (case when emdeon_tpl_flag in ('1','2') THEN 'Y'
                     ELSE                                   'N'
                     END) as emdeon_tpl_flag,

               (case when emdeon_commercial_flag in ('1','2') THEN 'Y'
                     ELSE                                          'N'
                     END) as emdeon_commercial_flag,
               'N' as emdeon_medicare_flag,
               NULL as emdeon_medicaid_flag,
               cust_id,
               client_npi1 as client_npi,
               name_type||'-'||hid||'-'||acctnum as emdeon_matchkey,
               service_date_begin,
               (CASE WHEN service_date_end IS NULL THEN service_date_begin
                     ELSE                               service_date_end
                     END) as service_date_end,
               (CASE WHEN name_type = 'P' THEN fname
                     ELSE                      g_fname
                     END) as fname,
               (CASE WHEN name_type = 'P' THEN mname
                     ELSE                      g_mname
                     END) as mname,
               (CASE WHEN name_type = 'P' THEN lname
                     ELSE                      g_lname
                     END) as lname,
               (CASE WHEN name_type = 'P' THEN dob
                     ELSE                      g_dob
                     END) as dob,
               (CASE WHEN name_type = 'P' THEN gender
                     ELSE                      g_gender
                     END) as gender,
               (CASE WHEN name_type = 'P' THEN ssn
                     ELSE                      g_ssn
                     END) as ssn,
               (CASE WHEN name_type = 'P' THEN addr1
                     ELSE                      g_addr1
                     END) as addr1,
               (CASE WHEN name_type = 'P' THEN addr2
                     ELSE                      g_addr2
                     END) as addr2,
               (CASE WHEN name_type = 'P' THEN city
                     ELSE                      g_city
                     END) as city,
               (CASE WHEN name_type = 'P' THEN state
                     ELSE                      g_state
                     END) as state,
               (CASE WHEN name_type = 'P' THEN zip5
                     ELSE                      g_zip5
                     END) as zip5

            FROM
               batch_insurance1_t
            WHERE
               emdeon_tpl_flag in ('1','2') or emdeon_commercial_flag in ('1','2') 

          ) as a

        JOIN

        (SELECT
                cust_id,
                client_npi,
                npi,
                tax_id,
                entity_name,
                entity_pay_to_street,
                entity_pay_to_city,
                entity_pay_to_state,
                entity_pay_to_zip,
                entity_information,
                entity_phone,
                (case when pin    is NULL THEN legacy
                      when legacy is NULL THEN ','||pin
                      else                     legacy||','||pin
                      end) as legacy_pin
        FROM
                insurance0_npi_t
        WHERE
                npi_com_ind = 1 
        ) as b

        ON (a.cust_id = b.cust_id AND a.client_npi = b.client_npi)

  ) as c
;



GET DIAGNOSTICS v_status = ROW_COUNT;
PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,6,'emdeon_out_t INSERT DONE');

SELECT INTO v_status                    count(*) from emdeon_out_t;
SELECT INTO v_status_unique             count(*) from (select distinct on (emdeon_matchkey) emdeon_matchkey from emdeon_out_t) as a; 
SELECT INTO v_status_tpl                sum(case when emdeon_tpl_flag = 'Y' THEN 1 else 0 end)            from emdeon_out_t;
SELECT INTO v_status_commercial 	sum(case when emdeon_commercial_flag = 'Y' THEN 1 else 0 end)     from emdeon_out_t;
SELECT INTO v_status_medicaid 		sum(case when emdeon_medicaid_flag IS NOT NULL THEN 1 else 0 end) from emdeon_out_t;
SELECT INTO v_status_medicare_fast 	sum(case when emdeon_medicare_flag = 'Y1' THEN 1 else 0 end)      from emdeon_out_t;
SELECT INTO v_status_medicare_slow 	sum(case when emdeon_medicare_flag = 'Y2' THEN 1 else 0 end)      from emdeon_out_t;


SELECT INTO v_status_chg                 round((sum(
                                              case when charges IS NULL AND balance IS NOT NULL THEN balance
                                                   when charges IS NOT NULL                     THEN charges
                                                   else                                              0 
                                                   end
                                              ))::numeric,2) from (select distinct on (hid,acctnum) hid,acctnum,charges,balance from batch_insurance1_t
                                                                  where emdeon_sent_date IS NOT NULL) as a;
SELECT INTO v_status_tpl_chg             round((sum(
                                              case when emdeon_tpl_flag in ('1','2') and charges IS NULL and balance IS NOT NULL THEN balance
                                                   when emdeon_tpl_flag in ('1','2') and charges IS NOT NULL                     THEN charges
                                                   else                                                                               0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_commercial_chg      round((sum(
                                              case when emdeon_commercial_flag in ('1','2') and charges IS NULL and balance IS NOT NULL THEN balance
                                                   when emdeon_commercial_flag in ('1','2') and charges IS NOT NULL                     THEN charges
                                                   else                                                                                      0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_medicaid_chg        round((sum(
                                              case when emdeon_medicaid_flag in ('1','2') and charges IS NULL and balance IS NOT NULL THEN balance
                                                   when emdeon_medicaid_flag in ('1','2') and charges IS NOT NULL                     THEN charges 
                                                   else                                                                                   0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_medicare_fast_chg   round((sum(
                                              case when emdeon_medicare_fast <> '1' or emdeon_medicare_fast IS NULL                   THEN 0 
                                                   when emdeon_medicare_flag in ('1','2') and charges IS NULL and balance IS NOT NULL THEN balance
                                                   when emdeon_medicare_flag in ('1','2') and charges IS NOT NULL                     THEN charges 
                                                   else                                                                                    0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;

SELECT INTO v_status_medicare_slow_chg   round((sum(
                                              case
                                                   when emdeon_medicare_fast = '1'                                                    THEN 0 
                                                   when emdeon_medicare_flag in ('1','2') and charges IS NULL and balance IS NOT NULL THEN balance
                                                   when emdeon_medicare_flag in ('1','2') and charges IS NOT NULL                     THEN charges 
                                                   else                                                                                    0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;


SELECT INTO v_status_bal                 round((sum(
                                              case when balance IS NULL AND charges IS NOT NULL THEN charges 
                                                   when balance IS NOT NULL                     THEN balance 
                                                   else                                              0
                                                   end
                                            ))::numeric,2) from (select distinct on (hid,acctnum) hid,acctnum,charges,balance from batch_insurance1_t
                                                                 where emdeon_sent_date IS NOT NULL) as a;
SELECT INTO v_status_tpl_bal             round((sum(
                                              case when emdeon_tpl_flag in ('1','2') and balance IS NULL and charges IS NOT NULL THEN charges 
                                                   when emdeon_tpl_flag in ('1','2') and balance IS NOT NULL                     THEN balance 
                                                   else                                                                               0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_commercial_bal      round((sum(
                                              case when emdeon_commercial_flag in ('1','2') and balance IS NULL and charges IS NOT NULL THEN charges 
                                                   when emdeon_commercial_flag in ('1','2') and balance IS NOT NULL                     THEN balance 
                                                   else                                                                                      0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_medicaid_bal        round((sum(
                                              case when emdeon_medicaid_flag in ('1','2') and balance IS NULL and charges IS NOT NULL THEN charges 
                                                   when emdeon_medicaid_flag in ('1','2') and balance IS NOT NULL                     THEN balance 
                                                   else                                                                                   0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;
SELECT INTO v_status_medicare_fast_bal   round((sum(
                                              case when emdeon_medicare_fast <> '1' or emdeon_medicare_fast IS NULL                   THEN 0
                                                   when emdeon_medicare_flag in ('1','2') and balance IS NULL and charges IS NOT NULL THEN charges 
                                                   when emdeon_medicare_flag in ('1','2') and balance IS NOT NULL                     THEN balance 
                                                   else                                                                                    0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;

SELECT INTO v_status_medicare_slow_bal   round((sum(
                                              case
                                                   when emdeon_medicare_fast = '1'                                                    THEN 0
                                                   when emdeon_medicare_flag in ('1','2') and balance IS NULL and balance IS NOT NULL THEN charges
                                                   when emdeon_medicare_flag in ('1','2') and balance IS NOT NULL                     THEN balance 
                                                   else                                                                                    0
                                                   end
                                            ))::numeric,2) from batch_insurance1_t;




-- STEP 3: UPDATE batch_status_insurance1_t TABLE;

  UPDATE batch_status_insurance1_t
    SET
      emdeon_out                 	= v_status,
      emdeon_unique_out          	= v_status_unique,
      emdeon_tpl_out             	= v_status_tpl,
      emdeon_commercial_out      	= v_status_commercial,
      emdeon_medicaid_out        	= v_status_medicaid,
      emdeon_medicare_slow_out   	= v_status_medicare_slow,
      emdeon_medicare_fast_out   	= v_status_medicare_fast,
      emdeon_chg_out            	= v_status_chg, 
      emdeon_bal_out             	= v_status_bal, 
      emdeon_tpl_chg_out          	= v_status_tpl_chg,
      emdeon_tpl_bal_out          	= v_status_tpl_bal,
      emdeon_commercial_chg_out    	= v_status_commercial_chg,
      emdeon_commercial_bal_out    	= v_status_commercial_bal,
      emdeon_medicaid_chg_out      	= v_status_medicaid_chg,
      emdeon_medicaid_bal_out      	= v_status_medicaid_bal,
      emdeon_medicare_slow_chg_out     	= v_status_medicare_slow_chg,
      emdeon_medicare_slow_bal_out     	= v_status_medicare_slow_bal,
      emdeon_medicare_fast_chg_out    	= v_status_medicare_fast_chg,
      emdeon_medicare_fast_bal_out   	= v_status_medicare_fast_bal


    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);
    
return v_status;
END;
$$ LANGUAGE plpgsql;
